package dao.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import db.common.DBHelper;
import model.common.Message;

public class MessageDao {
	public void addMessage(Message m) throws SQLException {
		Connection conn = DBHelper.getConnection();
		String sql = " "+" insert into tab_message " + "( messageName,messageDetail, "
				    + "  messageDate,messagePerson ) "
				    + " values(?,?,?,?) ";
		
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, m.getMessageName());
		ptmt.setString(2, m.getMessageDetail());
		ptmt.setDate(3, new java.sql.Date(m.getMessageDate().getTime()));
		ptmt.setString(4, m.getMessagePerson());
		
		ptmt.execute();
	}

	public void deleteMessage(Integer messageId) throws SQLException {
		Connection conn = DBHelper.getConnection();
		String sql = " delete from tab_message  where messageId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, messageId);
		ptmt.execute();
	}

	public void updateMessage(Message m) throws SQLException {
		Connection conn = DBHelper.getConnection();
		String sql = " update tab_message set messageName=?,messageDetail=?,messageDate=?,messagePerson=?"
				   + " where messageId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);

		ptmt.setString(1, m.getMessageName());
		ptmt.setString(2, m.getMessageDetail());
		ptmt.setDate(3, new java.sql.Date(m.getMessageDate().getTime()));
		ptmt.setString(4, m.getMessagePerson());
		ptmt.setInt(5, m.getMessageId());

		ptmt.execute();
	}

	public Message get(Integer messageId) throws SQLException {
		Message m = null;
		Connection conn = DBHelper.getConnection();
		String sql = " select * from tab_message where messageId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1,messageId);
		ResultSet rs = ptmt.executeQuery();
		while (rs.next()) {
			m = new Message();
			m.setMessageId(rs.getInt("messageId"));
			m.setMessageName(rs.getString("messageName"));
			m.setMessageDetail(rs.getString("messageDetail"));
			m.setMessageDate(rs.getDate("messageDate"));
			m.setMessagePerson(rs.getString("messagePerson"));
		}
		return m;
	}

	public List<Message> query(List<Map<String, Object>> params) throws SQLException {
		List<Message> result = new ArrayList<Message>();
		Connection conn = DBHelper.getConnection();
		StringBuffer sb = new StringBuffer();
		sb.append("select * from tab_message where 1=1 ");
		if (params != null && params.size() > 0) {
			for (int i = 0; i < params.size(); i++) {
				Map<String, Object> map = params.get(i);
				sb.append(" and " + map.get("name") + "  " + map.get("rela") + "  " + map.get("value"));
			}
		}
		PreparedStatement ptmt = conn.prepareStatement(sb.toString());

		System.out.println(sb.toString());
		ResultSet rs = ptmt.executeQuery();

		Message m = null;
		while (rs.next()) {
			m = new Message();
			m.setMessageId(rs.getInt("messageId"));
			m.setMessageName(rs.getString("messageName"));
			m.setMessageDetail(rs.getString("messageDetail"));
			m.setMessageDate(rs.getDate("messageDate"));
			m.setMessagePerson(rs.getString("messagePerson"));
			result.add(m);
		}

		return result;
	}

}
